/****************************************************************
Architecture:
Mohammad Ashraful Alam, Microsoft MVP 2007-2010
Portfolio: http://www.ashraful.net
***************************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using DaXDD.DataAccess.SqlClientDataAccess.Utility;
using System.Collections.Generic;
using DaXDD.DataContainers;
using DaXDD.DataAccess.Model.IDataAccess;
using DaXDD.DataContainers.Utility;

namespace DaXDD.DataAccess.Model.SqlClientDataAccess
{
    [System.ComponentModel.DataObject]
    public partial class EmployeeDAO:IEmployeeDAO
    {
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public bool DeleteEmployee(Employee toBeDeletedEmployee)
        {
            //Validate Input
            if (toBeDeletedEmployee.EmployeeId <= Employee.EmployeeIdMinValue)
                throw (new ArgumentOutOfRangeException("employeeId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@EmployeeId", SqlDbType.Int, 0, ParameterDirection.Input, toBeDeletedEmployee.EmployeeId);
            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_DELETE);

            //returns the number of affected rows
            int numberOfAffectedRows = DatabaseUtility.ExecuteNonQueryCmd(sqlCmd);

            return (numberOfAffectedRows == 0 ? false : true);
        }

        public bool DeleteEmployees(Collection<int> employeesIdsToDelete)
        {
            //Validate Input
            foreach (int employeeId in employeesIdsToDelete)
                if (employeeId <= Employee.EmployeeIdMinValue)
                    throw (new ArgumentOutOfRangeException("employeesIdsToDelete"));

            //performing deletion operation //

            string xmlString = DatabaseUtility.FormatXmlForIdArray(employeesIdsToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@employeeIds", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_DELETE_LIST);
            //returns the number of affected rows
            int numberOfAffectedRows = (int)DatabaseUtility.ExecuteScalarCmd(sqlCmd);
            return (numberOfAffectedRows == employeesIdsToDelete.Count ? true : false);

        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public Employee GetEmployeeByEmployeeId(int employeeId)
        {
            //Validate Input
            if (employeeId <= Employee.EmployeeIdMinValue)
                throw (new ArgumentOutOfRangeException("employeeId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@EmployeeId", SqlDbType.Int, 0, ParameterDirection.Input, employeeId);
            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEE_BY_EMPLOYEEID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(Employee.GenerateEmployeeCollectionFromReader);
            List<Employee> objCollection = ((List<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public List<Employee> GetEmployeesByReportsTo(int? reportsTo)
        {
            //Validate Input
            if (reportsTo == null)
                return GetAllEmployees();
            else if (reportsTo <= Employee.EmployeeIdMinValue)
                throw (new ArgumentOutOfRangeException("reportsTo"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@reportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo);

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(Employee.GenerateEmployeeCollectionFromReader);
            List<Employee> objCollection = ((List<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public List<Employee> GetEmployeesByReportsToPaged(int? reportsTo, string orderBy, int startRowIndex, int maximumRows)
        {
            //Validate Input
            //If reportsTo is less than the MinValue then return all records of the corresponding table, regarding the page size
            if (reportsTo == null)
                return GetAllEmployeesPaged(orderBy, startRowIndex, maximumRows);
            else if (reportsTo <= Employee.EmployeeIdMinValue)
                throw (new ArgumentOutOfRangeException("reportsTo"));

            //If there is no sorting parameter, then by default consider the primary key as the sorting field
            if (string.IsNullOrEmpty(orderBy))
                orderBy = "EmployeeId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@reportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO_PAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(Employee.GenerateEmployeeCollectionFromReader);
            List<Employee> objCollection = ((List<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public int GetEmployeesByReportsToPagedCount(int? reportsTo, string orderBy, int startRowIndex, int maximumRows)
        {
            //Validate Input
            //If reportsTo is less than the MinValue then return number of all records of the corresponding table, regardless of page size
            if (reportsTo == null)
                return GetAllEmployeesPagedCount(orderBy, startRowIndex, maximumRows);
            else if (reportsTo <= Employee.EmployeeIdMinValue)
                throw (new ArgumentOutOfRangeException("reportsTo"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO_PAGED_COUNT);
            int totalCount = (int)DatabaseUtility.ExecuteScalarCmd(sqlCmd);
            return totalCount;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public List<Employee> GetAllEmployees()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETALLEMPLOYEES);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(Employee.GenerateEmployeeCollectionFromReader);
            List<Employee> objCollection = ((List<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public List<Employee> GetAllEmployeesPaged(string orderBy, int startRowIndex, int maximumRows)
        {
            //Validate Input
            if (string.IsNullOrEmpty(orderBy))
                orderBy = "EmployeeId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETALLEMPLOYEES_PAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(Employee.GenerateEmployeeCollectionFromReader);
            List<Employee> objCollection = ((List<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public int GetAllEmployeesPagedCount(string orderBy, int startRowIndex, int maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETALLEMPLOYEES_PAGED_COUNT);
            DatabaseUtility.ExecuteScalarCmd(sqlCmd);
            int totalCount = (int)DatabaseUtility.ExecuteScalarCmd(sqlCmd);
            return totalCount;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, false)]
        public int CreateNewEmployee(Employee newEmployee)
        {
            // Validate Parameters
            if (newEmployee == null)
                throw (new ArgumentNullException("newEmployee"));

            // Validate Primary key value
            if (newEmployee.EmployeeId > Employee.EmployeeIdMinValue)
                throw (new ArgumentOutOfRangeException("newEmployee"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@LastName", SqlDbType.NVarChar, 20, ParameterDirection.Input, newEmployee.LastName);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@FirstName", SqlDbType.NVarChar, 10, ParameterDirection.Input, newEmployee.FirstName);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Title", SqlDbType.NVarChar, 30, ParameterDirection.Input, newEmployee.Title);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@TitleOfCourtesy", SqlDbType.NVarChar, 25, ParameterDirection.Input, newEmployee.TitleOfCourtesy);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@BirthDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newEmployee.BirthDate);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@HireDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newEmployee.HireDate);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Address", SqlDbType.NVarChar, 60, ParameterDirection.Input, newEmployee.Address);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@City", SqlDbType.NVarChar, 15, ParameterDirection.Input, newEmployee.City);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Region", SqlDbType.NVarChar, 15, ParameterDirection.Input, newEmployee.Region);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@PostalCode", SqlDbType.NVarChar, 10, ParameterDirection.Input, newEmployee.PostalCode);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Country", SqlDbType.NVarChar, 15, ParameterDirection.Input, newEmployee.Country);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@HomePhone", SqlDbType.NVarChar, 24, ParameterDirection.Input, newEmployee.HomePhone);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Extension", SqlDbType.NVarChar, 4, ParameterDirection.Input, newEmployee.Extension);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Photo", SqlDbType.Image, 0, ParameterDirection.Input, newEmployee.Photo);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Notes", SqlDbType.NText, 0, ParameterDirection.Input, newEmployee.Notes);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReportsTo", SqlDbType.Int, 0, ParameterDirection.Input, newEmployee.ReportsTo);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@PhotoPath", SqlDbType.NVarChar, 255, ParameterDirection.Input, newEmployee.PhotoPath);

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_CREATE);
            DatabaseUtility.ExecuteNonQueryCmd(sqlCmd);
            return ((int)sqlCmd.Parameters["@ReturnValue"].Value);

        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, false)]
        public bool UpdateEmployee(Employee updatedEmployee)
        {
            // Validate Parameters
            if (updatedEmployee == null)
                throw (new ArgumentNullException("updatedEmployee"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@EmployeeId", SqlDbType.Int, 0, ParameterDirection.Input, updatedEmployee.EmployeeId);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@LastName", SqlDbType.NVarChar, 20, ParameterDirection.Input, updatedEmployee.LastName);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@FirstName", SqlDbType.NVarChar, 10, ParameterDirection.Input, updatedEmployee.FirstName);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Title", SqlDbType.NVarChar, 30, ParameterDirection.Input, updatedEmployee.Title);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@TitleOfCourtesy", SqlDbType.NVarChar, 25, ParameterDirection.Input, updatedEmployee.TitleOfCourtesy);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@BirthDate", SqlDbType.DateTime, 0, ParameterDirection.Input, updatedEmployee.BirthDate);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@HireDate", SqlDbType.DateTime, 0, ParameterDirection.Input, updatedEmployee.HireDate);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Address", SqlDbType.NVarChar, 60, ParameterDirection.Input, updatedEmployee.Address);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@City", SqlDbType.NVarChar, 15, ParameterDirection.Input, updatedEmployee.City);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Region", SqlDbType.NVarChar, 15, ParameterDirection.Input, updatedEmployee.Region);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@PostalCode", SqlDbType.NVarChar, 10, ParameterDirection.Input, updatedEmployee.PostalCode);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Country", SqlDbType.NVarChar, 15, ParameterDirection.Input, updatedEmployee.Country);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@HomePhone", SqlDbType.NVarChar, 24, ParameterDirection.Input, updatedEmployee.HomePhone);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Extension", SqlDbType.NVarChar, 4, ParameterDirection.Input, updatedEmployee.Extension);

            int length = 0;

            //if (updatedEmployee.Photo != null)
            //{
            //    length = updatedEmployee.Photo.Length;
            //}

            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Photo", SqlDbType.Image, length, ParameterDirection.Input, updatedEmployee.Photo);

            //length = 0;
            //if (updatedEmployee.Notes != null)
            //{
            //    length = updatedEmployee.Notes.Length;
            //}

            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@Notes", SqlDbType.NText, length, ParameterDirection.Input, updatedEmployee.Notes);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReportsTo", SqlDbType.Int, 0, ParameterDirection.Input, updatedEmployee.ReportsTo);
            DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@PhotoPath", SqlDbType.NVarChar, 255, ParameterDirection.Input, updatedEmployee.PhotoPath);

            DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_UPDATE);
            int numberOfAffectedRows = DatabaseUtility.ExecuteNonQueryCmd(sqlCmd);
            return (numberOfAffectedRows == 0 ? false : true);

        }

        #region Constants and Default Values

        //Employee entity related constants, which includes relevant stored procedure names

        // -- create method related constants -- //
        const string SP_EMPLOYEES_CREATE = "Employees_CreateNewEmployee";

        // -- update method related constants -- //
        const string SP_EMPLOYEES_UPDATE = "Employees_UpdateEmployee";
        
        // -- delete method related constants -- //
        const string SP_EMPLOYEES_DELETE = "Employees_DeleteEmployee";
        const string SP_EMPLOYEES_DELETE_LIST = "Employees_DeleteEmployees";

        //-- get method related constants -- //

        //get by primary key  stored procedure
        const string SP_EMPLOYEES_GETEMPLOYEE_BY_EMPLOYEEID = "Employees_GetEmployeeByEmployeeId";

        //get by foreign key stored procedures
        const string SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO = "Employees_GetEmployeesByReportsTo";
        const string SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO_PAGED = "Employees_GetEmployeesByReportsTo_Paged";
        const string SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO_PAGED_COUNT = "Employees_GetEmployeesByReportsTo_Paged_Count";

        //'get all'  stored procedures
        const string SP_EMPLOYEES_GETALLEMPLOYEES = "Employees_GetAllEmployees";
        const string SP_EMPLOYEES_GETALLEMPLOYEES_PAGED = "Employees_GetAllEmployees_Paged";
        const string SP_EMPLOYEES_GETALLEMPLOYEES_PAGED_COUNT = "Employees_GetAllEmployees_Paged_Count";
        
        #endregion

    }//end of Employee class
}
